package db

import (
	sql2 "database/sql"
	"d-serv-go/server/core/bean"
	"d-serv-go/server/core/log"
	strutils "d-serv-go/server/core/util/stringutils"
	"github.com/shopspring/decimal"
	"regexp"
	"strconv"
	"strings"
)

type Callback func(columns []*bean.Bean, data *bean.Bean) int

type SqlExecutor struct {
	ds Ds // 数据源
}

func (c *SqlExecutor) QueryCall(sql string, args []interface{}, call Callback) ([]*bean.Bean, error) {
	var (
		query *sql2.Rows
		err   error
	)
	ctx := Ctx()
	db, tx := ctx.Db("")
	if db != nil {
		query, err = db.Query(sql, args...)
	} else if tx != nil {
		query, err = tx.Query(sql, args...)
	}
	defer func() {
		if query != nil {
			query.Close()
		}
	}()

	if err != nil {
		log.Error("执行sql:%v, 异常：%v", sql, err)
		return nil, err
	} else {
		log.Info("sql executor,执行sql:%v", sql)
	}
	colTypes, _ := query.ColumnTypes()
	//values是每个列的值，这里获取到byte里
	values := make([][]byte, len(colTypes))
	//query.Scan的参数，因为每次查询出来的列是不定长的，用len(cols)定住当次查询的长度
	scans := make([]interface{}, len(values))
	//让每一行数据都填充到[][]byte里面
	for i := range values {
		scans[i] = &values[i]
	}
	//最后得到的list
	list := make([]*bean.Bean, 0)
	for query.Next() { //循环，让游标往下推
		if err := query.Scan(scans...); err != nil {
			log.Error(err)
		}
		bean := bean.NewBean("")
		for i, v := range values {
			if (i == 0) { // 设置虚拟主键
				bean.SetId(string(v))
			}
			colType := colTypes[i]
			switch colType.DatabaseTypeName() {
			case "CHAR", "VARCHAR", "TINYTEXT", "TEXT", "MEDIUMTEXT", "LONGTEXT", "NVARCHAR":
				bean.Set(colType.Name(), string(v))
			case "TINYINT", "SMALLINT", "MEDIUMINT", "INT", "BIGINT", "BIT":
				if i, err := strconv.Atoi(string(v)); err == nil {
					bean.Set(colType.Name(), i)
				}
			case "DECIMAL":
				v, _ := decimal.NewFromString(string(v))

				bean.Set(colType.Name(), int(v.IntPart()))
			case "BOOL":
				v, _ := strconv.ParseBool(string(v))
				bean.Set(colType.Name(), v)
			case "BINARY", "BLOB":
				bean.Set(colType.Name(), v)
			case "TIMESTAMP":
				bean.Set(colType.Name(), v)
			case "DATETIME", "TIME":
				bean.Set(colType.Name(), string(v))
			case "YEAR":
				bean.Set(colType.Name(), string(v))
			default:
				bean.Set(colType.Name(), string(v))
			}
		}
		list = append(list, bean)
		if call != nil {
			call(nil, bean)
		}
	}
	return list, nil
}

func (c *SqlExecutor) Query(sql string, values []interface{}, call Callback) ([]*bean.Bean, error) {
	return c.QueryPage(sql, 0, -1, values, call)
}

func (c *SqlExecutor) QueryOne(sql string, values []interface{}) (*bean.Bean, error) {
	list, err := c.QueryPage(sql, 0, 1, values, nil)
	if err != nil {
		return nil, err
	}
	if len(list) > 0 {
		return list[0], nil
	}
	return nil, nil
}

func (c *SqlExecutor) QueryById(sql string, values []interface{}) (*bean.Bean, error) {
	list, err := c.QueryPage(sql, 0, 1, values, nil)
	if err != nil {
		return nil, err
	}
	if len(list) > 0 {
		return list[0], nil
	}
	return nil, nil
}

func (c *SqlExecutor) Count(sql string, values []interface{}) (int, error) {
	//如果存在子查询则直接算最终的结果合计数
	reg, _ := regexp.Compile(".*\\(\\s*((?i)SELECT)\\s+.*")
	if reg.Match([]byte(sql)) {
		sql = "select count(*) COUNT_ from (" + sql + ") t"
	} else {
		// 通过正则表达式，将参数sql语句中的包含大写字母的FROM替换为小写的from，
		// 以便下面的：sql.indexOf(" from ")能得到正确结果
		sql = strings.ReplaceAll(sql, "\\s+([f|F][R|r][o|O][m|M])\\s+", " from ")
		sql = strings.ReplaceAll(sql, "\\s+([g|G][R|r][o|O][u|U][p|P])\\s+[b|B][y|Y]\\s+", " group by ")
		sql = strings.ReplaceAll(sql, "(order by|ORDER BY).*$", "")
		// 对于是图表的分页展示处理。将分组的sql在加一层count。
		if strings.Index(sql, " group by ") > 0 {
			sql = " from (" + sql + ")  contTmp"
		}
		// 预处理SQL语句，去除order by 部分
		pos := strings.Index(sql, " from ")
		sql = "select count(*) COUNT_ " + sql[pos:]
	}
	count, err := c.QueryOne(sql, values)
	if err != nil {
		return 0, err
	}
	return count.GetInt("COUNT_"), nil
}

func (c *SqlExecutor) QueryPage(sql string, pageNum int, count int, values []interface{}, call Callback) ([]*bean.Bean, error) {
	if count > 0 { // 分页处理
		pageSql := strutils.NewStringBuilder("")
		if values != nil { // psql
			pageSql.Append(sql).Append(" limit ?,?")
			if pageNum <= 0 {
				values = append(values, pageNum*count)
			} else {
				values = append(values, (pageNum-1)*count)
			}
			values = append(values, count)
		} else {
			pageSql.Append(sql).Append(" limit ")
			if pageNum <= 0 {
				pageSql.Append(strconv.Itoa(pageNum * count))
			} else {
				pageSql.Append(strconv.Itoa((pageNum - 1) * count))
			}
			pageSql.Append(",").Append(strconv.Itoa(count))
		}
		sql = pageSql.ToString()
	}
	return c.QueryCall(sql, values, call)
}

func (c *SqlExecutor) Execute(sql string, values []interface{}) (int64, error) {
	var (
		result sql2.Result
		err    error
	)
	db, tx := Ctx().Db("")
	if db != nil {
		result, err = db.Exec(sql, values...)
	} else if tx != nil {
		result, err = tx.Exec(sql, values...)
	}
	if err != nil {
		return 0, err
	}
	return result.RowsAffected()
}

// 目前同上一样
func (c *SqlExecutor) ExecuteBatch(sql string, values []interface{}) (int64, error) {
	var (
		result sql2.Result
		err    error
	)
	db, tx := Ctx().Db("")
	if db != nil {
		result, err = db.Exec(sql, values...)

	} else if tx != nil {

		result, err = tx.Exec(sql, values...)
	}
	if err != nil {
		return 0, err
	}
	return result.RowsAffected()
}
